package br.edu.ifpb.classesDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.edu.ifpb.conexao.ConnectionFactory;
import br.edu.ifpb.entidades.Confirmacao;
import br.edu.ifpb.sistema.DataHorario;

public class ConfirmacaoDAO implements DAO<Confirmacao> {
	
	Connection connection;
	
	public ConfirmacaoDAO(){
		connection = new ConnectionFactory().getConnection();
	}
	
	@Override
	public boolean insert(Confirmacao value) throws SQLException {
		
		PreparedStatement stmt = connection.prepareStatement("insert into tb_confirmacao(id_refeicao) values ("+ value.getId_refeicao() +")");
		stmt.execute();
		
		return true;
	}

	@Override
	public ArrayList<Confirmacao> getAll() throws SQLException {
		
		DataHorario d = new DataHorario();
		ArrayList<Confirmacao> lista = new ArrayList<Confirmacao>();
		
		PreparedStatement stmt = connection.prepareStatement("select * from tb_confirmacao where data_refeicao like '" + d.getData() + "%'");
		ResultSet rs = stmt.executeQuery();
		Confirmacao c;
		
		while (rs.next()) {
			c = new Confirmacao();
			
			int id_refeicao = rs.getInt("id_refeicao");
			c.setId_refeicao(id_refeicao);
			
		 
			
			PreparedStatement stmt2 = connection.prepareStatement("select * from tb_refeicao where id_refeicao = "+ id_refeicao);
			ResultSet rs2 = stmt2.executeQuery();
			
			while (rs2.next()){
				
				int id_usuario = rs2.getInt("id_usuario");
				
				PreparedStatement stmt3 = connection.prepareStatement("select nome from tb_usuario where id_usuario = "+ id_usuario);
				ResultSet rs3 = stmt3.executeQuery();
				
				if (rs3.next()){
					c.setNome_usuario(rs3.getString("nome"));
					c.setId_usuario(id_usuario);
				}
				
			}
			
			
			c.setData_confirmacao(rs.getString("data_refeicao"));
			
			lista.add(c);
		}
		
		return lista;
		
	}

	@Override
	public Confirmacao get(int id) throws SQLException {
		
		Confirmacao c = null;
		
		String sql = "select * tb_confirmacao where id_confirmacao = " + id;
		PreparedStatement stmt = connection.prepareStatement(sql);
		
		ResultSet rs = stmt.executeQuery();
		
		if (rs.next()){
			
			c = new Confirmacao();
			
			c.setId_refeicao(rs.getInt("id_refeicao"));
			c.setData_confirmacao(rs.getString("data_refeicao"));
			c.setId_confirmacao(id); 
			
		}
		
		return c;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		try {
			
			String sql = "delete from tb_confirmacao where id_confirmacao = " + id;
			PreparedStatement stmt = connection.prepareStatement(sql);
			
			stmt.execute();
			
			return true;
			
		} catch (SQLException e){
			return false;
		}
	}

	@Override
	public void update(Confirmacao value) throws SQLException {
		
		String sql = "update tb_confirmacao set id_refeicao = ? where id_confirmacao = ?";
		PreparedStatement stmt = connection.prepareStatement(sql);
		
		stmt.setInt(1, value.getId_refeicao());
		stmt.setInt(2, value.getId_confirmacao());
				
		stmt.execute();
		
	}

	@Override
	public void disconnect() throws SQLException {
		
		this.connection.close();
		
	}

}
